﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using InvestmentIntelligence.DB;
using InvestmentIntelligence.Data.Repository.EFImplementation;
using InvestmentIntelligence.DbModel.Models;

namespace InvestmentIntelligence.Data.Repository.SqlImplementation
{
    public class ExposureConsistencyCheckRepository: IIPRepository<SuspiciousExposure>, IExposureConsistencyCheckRepository
    {
        #region SQL
        private const string SaveSuspiciousExposuresCommand = @"
SELECT op.FundBookEntityId, op.SecurityId, op.[Date], ROUND( op.CloseQuantity * ts.[Close] * (fxS.FxRateInUsd/fxF.FxRateInUsd), 4) AS SuggestedExposure,
	ABS((op.CloseExposure - ROUND( op.CloseQuantity * ts.[Close] * (fxS.FxRateInUsd/fxF.FxRateInUsd), 4))/NULLIF(op.CloseExposure, 0))*100
	AS DifferencePercent,
	CURRENT_TIMESTAMP AS ProcessingTimestamp 

  FROM [TradingData].[OriginalPosition] op
  
  LEFT JOIN MarketData.[Security] s
  ON op.SecurityId = s.Id
  
  LEFT JOIN TradingData.FundBookEntity fb
  ON op.FundBookEntityId = fb.Id
  
  LEFT JOIN MarketData.TimeSerie ts
  ON op.SecurityId = ts.SecurityId AND op.[Date] = ts.[Date]
  
  LEFT JOIN MarketData.FxSerie fxS
  ON s.CurrencyId = fxS.CurrencyId AND fxS.[Date] = ts.[Date]
  
  LEFT JOIN MarketData.FxSerie fxF
  ON fb.CurrencyId = fxF.CurrencyId AND fxF.[Date] = ts.[Date]
  
  WHERE op.[Date] > @FromDate AND
  op.CloseExposure <> ROUND( op.CloseQuantity * ts.[Close] * (fxS.FxRateInUsd/fxF.FxRateInUsd), 4)
  AND ABS((op.CloseExposure - ROUND( op.CloseQuantity * ts.[Close] * (fxS.FxRateInUsd/fxF.FxRateInUsd), 4))/NULLIF(op.CloseExposure, 0))*100 > @DifferenceThreshold
";
        #endregion

        public IEnumerable<SuspiciousExposure> GetSuspiciousExposures(DateTime fromDate, decimal differencePercentThreshold)
        {
            using (var dc = new DataConnection())
            {
                dc.Parameters.AddWithValue("@FromDate", fromDate);
                dc.Parameters.AddWithValue("@DifferenceThreshold", differencePercentThreshold);
                return dc.Query<SuspiciousExposure>(SaveSuspiciousExposuresCommand, CommandType.Text).ToArray();
            }
        }
    }
}
